﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;

using NHibernate;
using BEGDC.Utility.NHibernate;

using BEGDC.Office.Interface;
using BEGDC.Office.Library;

namespace BEGDC.MIS.Office.Excel
{
    public abstract class FixedExcelService : ExcelService
    {
        public FixedExcelService()
        {
            definition = XmlDefinition.CreateInstance(ServiceName);
        }

        private XmlDefinition definition;
        protected virtual XmlDefinition Definition
        {
            get
            {
                return definition;
            }
        }        
        
        public override DataSet GetDataFromFile(string fileName)
        {
            DataSet result;

            if (definition == null)
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0001", "Definition isn't found.");

            if (!System.IO.File.Exists(fileName))
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0002", "Tagert office file isn't found.");

            try
            {
                result = new DataSet();

                using (OfficeSession session = SessionFactory.OpenSession(OfficeSessionType.Excel))
                {
                    IExcelSession excel_session = (IExcelSession)session;

                    if (excel_session == null)
                        throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0004", "Session can't be created, the excel service is busy now.");

                    excel_session.Open(fileName);

                    //Read Table

                    for (int i = 0; i < definition.Tables.Count; i++)
                    {
                        XmlTable xml_table = definition.Tables[i];
                        DataTable table = new DataTable(xml_table.Name);

                        excel_session.SelectSheet(xml_table.Sheet);

                        if (xml_table.Rows.Count > 0)
                        {
                            XmlRow xml_row = xml_table.Rows[0];
                            for (int k = 0; k < xml_row.Elements.Count; k++)
                            {
                                Element element = xml_row.Elements[k];
                                DataColumn col = table.Columns.Add(element.Name, GetType(element.Type));
                                if (element.IsKey)
                                    col.ExtendedProperties.Add("Key", "true");
                                if (element.IsConvert)
                                    col.ExtendedProperties.Add("Convert", "true");
                                if (element.FixedValue != string.Empty)
                                    col.ExtendedProperties.Add("FixedValue", element.FixedValue);
                            }
                        }

                        //Read Rows
                        for (int j = 0; j < xml_table.Rows.Count; j++)
                        {
                            XmlRow xml_row = xml_table.Rows[j];

                            DataRow row = table.NewRow();

                            for (int k = 0; k < xml_row.Elements.Count; k++)
                            {
                                Element element = xml_row.Elements[k];
                                string str_value = excel_session.GetValue(element.Position);
                                //if (element.Name != "Province")
                                //{
                                    row[element.Name] = ConvertByType(str_value, element.Type);
                                //}
                            }

                            table.Rows.Add(row);
                        }

                        result.Tables.Add(table);

                        //validation
                        base.Validate(xml_table, result.Tables[xml_table.Name]);
                    }
                }

                return result;
            }
            catch(BEGDC.Utility.ExceptionManagement.GDCException gdce)
            {
                throw gdce;
            }
            catch (Exception err)
            {
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0003", "Error is found when reading data from excel file.", err);
            }
            finally
            {
            }
        }

        public override void SaveDataToFile(DataSet saveData, string fileName)
        {
            if (definition == null)
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0001", "Definition isn't found.");

            if (!System.IO.File.Exists(fileName))
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0002", "Tagert office file isn't found.");

            try
            {
                using (OfficeSession session = SessionFactory.OpenSession(OfficeSessionType.Excel))
                {
                    IExcelSession excel_session = (IExcelSession)session;

                    if (excel_session == null)
                        throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0004", "Session can't be created, the excel service is busy now.");

                    excel_session.Open(fileName);

                    //Read Table

                    for (int i = 0; i < definition.Tables.Count; i++)
                    {
                        XmlTable xml_table = definition.Tables[i];
                        if (!saveData.Tables.Contains(xml_table.Name))
                            continue;

                        DataTable table = saveData.Tables[xml_table.Name];
                        excel_session.SelectSheet(xml_table.Sheet);
                        //Read Rows
                        for (int j = 0; j < xml_table.Rows.Count; j++)
                        {
                            XmlRow xml_row = xml_table.Rows[j];

                            string filter = "";

                            for (int k = 0; k < xml_row.Elements.Count; k++)
                            {
                                Element element = xml_row.Elements[k];
                                if (element.IsFixed)
                                {
                                    string str_value = excel_session.GetValue(element.Position);
                                    if ((str_value != null) && (str_value != string.Empty))
                                    {
                                        filter += " And "+element.Name+"="+GetSqlByType(str_value, element.Type); 
                                    }
                                }
                            }

                            DataRow row = null;

                            if (filter.Length > 0)
                            {
                                filter = filter.Substring(5);
                                DataRow[] rows = table.Select(filter);
                                if (rows.Length > 0)
                                    row = rows[0];
                            }
                            else
                            {
                                if (j < table.Rows.Count)
                                    row = table.Rows[j];
                            }

                            if (row != null)
                            {
                                for (int k = 0; k < xml_row.Elements.Count; k++)
                                {
                                    Element element = xml_row.Elements[k];
                                    if (!element.IsFixed)
                                    {
                                        string str_value = row[element.Name].ToString();
                                        excel_session.SetValue(element.Position, str_value);
                                    }
                                }
                            }
                        }

                    }
                }
            }
            catch (BEGDC.Utility.ExceptionManagement.GDCException gdce)
            {
                throw gdce;
            }
            catch (Exception err)
            {
                throw new BEGDC.Utility.ExceptionManagement.GDCException("01.0003", "Error is found when reading data from excel file.", err);
            }
        }

        private Type GetType(string strType)
        {
            Type type;
            switch (strType.ToLower())
            {
                case "int":
                    type = typeof(int);
                    break;
                case "long":
                    type = typeof(long);
                    break;
                case "decimal":
                    type = typeof(decimal);
                    break;
                case "datatime":
                    type = typeof(DateTime);
                    break;
                case "bool":
                    type = typeof(bool);
                    break;
                default:
                    type = typeof(string);
                    break;
            }
            return type;
        }

        private string GetSqlByType(string strValue, string strType)
        {
            string value = strValue;
            try
            {
                switch (strType.ToLower())
                {
                    case "int":
                        value = strValue;
                        break;
                    case "long":
                        value = strValue;
                        break;
                    case "decimal":
                        value = strValue;
                        break;
                    case "datatime":
                        value = "'" + strValue + "'";
                        break;
                    case "bool":
                        if ((strValue.ToLower() == "true") || (strValue.ToLower() == "y") || (strValue.ToLower() == "是"))
                            value = "1";
                        else
                            value = "0";
                        break;
                    default:
                        value = "'"+strValue+"'";
                        break;
                }
                return value;
            }
            catch (Exception err)
            {
                throw new BEGDC.Utility.ExceptionManagement.GDCException("00.0000", "Excel data format isn't correct.", err);
            }
        }

        private object ConvertByType(string strValue, string strType)
        {
            object value;
            if ((strValue == null) || (strValue == string.Empty))
            {
                return System.DBNull.Value;
            }
            try
            {
                switch (strType.ToLower())
                {
                    case "int":
                        value = Convert.ToInt32(strValue);
                        break;
                    case "long":
                        value = Convert.ToInt64(strValue);
                        break;
                    case "decimal":
                        value = Convert.ToDecimal(strValue);
                        break;
                    case "datatime":
                        value = Convert.ToDateTime(strValue);
                        break;
                    case "bool":
                        value = Convert.ToBoolean(strValue);
                        break;
                    default:
                        value = strValue;
                        break;
                }
                return value;
            }
            catch(Exception err)
            {
                throw new BEGDC.Utility.ExceptionManagement.GDCException("00.0000", "Excel data format isn't correct.", err);
            }
        }
    }
}
